1 Setup

To work through these examples, you will need the following on your own computer:

  1. The datasets (see this link for a description)
  2. Microsoft Excel (you have a licence for this via the university)

1.1 Boxplot

Excel does not have an option to produce a 1D scatterplot, natively. We therefore produce only a boxplot (also known as a box-and-whisker plot)

You cannot easily convert the data in toothgrowth.tab to the format you need, in order to create a boxplot, using only Excel. The original file presents data in long format (one row per observation). To construct a boxplot in Excel, the data must be in wide format. This requires either a lot of function programming in Excel, or the use of the Power Query plugin, both of which are beyond the scope of this workshop.

We are instead providing a pre-prepared dataset: toothgrowth_wide.tab so that you can construct a boxplot in Excel:

Click here to toggle details of how we modified the data using R

Being a statistical programming package, R has several tools for reshaping datasets. We used the tidyr package function pivot_wider and some dplyr convenience functions to modify the data, then save it, using the commands below:

widedata = ToothGrowth %>%
  group_by(supp, dose) %>%
  mutate(row=row_number()) %>%
  pivot_wider(names_from=c(supp, dose), values_from=len)
write.table(widedata, "data/toothgrowth_wide.tab",
  sep="\t", row.names=FALSE)

Our goal is to show how the measured guinea pig tooth growth varies by combination of supplement and supplement dosage. We could approach this in any of several ways, but here we want to treat each supplement as a category or factor, and each dosage as a category or factor. We’d like to see the distribution of measured tooth lengths conditioned on these explanatory variables.

What we’re looking for is a visual representation of the variation in the dataset, for each combination of supplement and dosage. Normally, a 1D scatterplot is a good way to visualise the raw data, with a boxplot/box-and-whisker plot to represent summary statistics. Sadly, Excel cannot represent a 1D scatterplot easily, so we only show the box-and-whisker plot.

  • Create a new BlankWorkbook` in Excel
Create a blank workbook in `Excel`

Figure 1.1: Create a blank workbook in Excel

Create a blank workbook in `Excel`

Figure 1.2: Create a blank workbook in Excel

  • Use File \(\rightarrow\) Import to begin the file import wizard.
  • Navigate to and select the toothgrowth_wide.tab file
Select data file for import

Figure 1.3: Select data file for import

Select data file for import

Figure 1.4: Select data file for import

  • Using the wizard, identify the file delimiter, set the column datatypes, and import the data into the current worksheet.
Select import options

Figure 1.5: Select import options

Select import options

Figure 1.6: Select import options

Select import options

Figure 1.7: Select import options

Select import options

Figure 1.8: Select import options

Select import options

Figure 1.9: Select import options

At this point the data are in six columns, identified as a combination of supplement (VC or OJ) and supplement dose (one of 0.5, 1, or 2 mg/day). Each measurement is tooth length from a single guinea pig.

To create the box-and-whisker plot, select the complete dataset (including headers), then click on Insert and select the statistical plots button from the ribbon, then the Box-and-Whisker option in the dropdown menu.

Select box-and-whisker plot

Figure 1.10: Select box-and-whisker plot

Select box-and-whisker plot

Figure 1.11: Select box-and-whisker plot

This will generate a new graph in your worksheet.

The graph is, by default, not informatively-labelled.

  • Use the Add Chart Element \(\rightarrow\) Legend \(\rightarrow\) Right menu to add a legend to the graph.
  • Double-click on the chart title to edit it
  • Select and delete the uninformative horizontal axis label
Tidy plot details

Figure 1.12: Tidy plot details

Tidy plot details

Figure 1.13: Tidy plot details

Tidy plot details

Figure 1.14: Tidy plot details

This looks OK, and the interpretation is straightforward enough:

  • Increasing supplement dose looks to be correlated with tooth growth
  • At 2mg/day both supplements are associated with similar tooth growth
  • There appears to be a monotonic relationship between tooth length and dosage, for both supplements
  • At lower dosages, OJ appears to be associated with greater tooth length
  • The mean (cross) and median (line) values do not coincide, so the data may be skewed/non-normal
  • There is a single outlier in the VC_1 data

but we could do better with the colours. As the supplement choice is categorical data, we can colour OJ and VC consistently, but differently: orange for OJ and green for VC. We can then use different intensities (or saturation) to imply variation in dosage, so that more intense colours imply stronger dosage.

To change the colour of a box/whisker combination, click on the box and choose Format Data Series... then change the colours as appropriate.

Recolour data series

Figure 1.15: Recolour data series

Recolour data series

Figure 1.16: Recolour data series

Recolour data series

Figure 1.17: Recolour data series

With the Format Data Series side panel open, you can colour each of the other data series, to obtain a final graph.

Final graph

Figure 1.18: Final graph

1.2 Linear regression